﻿using MySql.Data.MySqlClient;
using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace MountSilverLibraryRemake
{
    public partial class CheckLibrary : Form
    {
        private easySorter sorter;
        private bool IsDouble(string str)
        {
            try
            {
                Double i = Convert.ToDouble(str);
                return true;
            }
            catch
            {
                return false;
            }
        }

        public CheckLibrary()
        {
            InitializeComponent();
            sorter = new easySorter();
            this.libraryStatus.ListViewItemSorter = sorter;
            sorter.SortOrder = SortOrder.Ascending;
        }
        public void flushLibraryStatus()
        {
            libraryStatus.Items.Clear();
            String[] ColumnsHeaderString = { "name", "version", "ISBN", "author", "republic", "storage", "baseprice", "discount", "finalprice" };
            String loginConnection = "server=localhost;user id=root;password=root;database=mountsilverlibraryremake";
            MySqlConnection connectionLogin = new MySqlConnection(loginConnection);
            try
            {
                connectionLogin.Open();
            }
            catch (Exception exception)
            {
                MessageBox.Show(exception.StackTrace);
            }
            String libraryCommand = "select * from library";
            MySqlCommand commandLogin = new MySqlCommand(libraryCommand, connectionLogin);
            MySqlDataReader librarydr = commandLogin.ExecuteReader();
            while (librarydr.Read())
            {
                ListViewItem listToAdd = new ListViewItem();
                listToAdd.Text = librarydr[ColumnsHeaderString[0]].ToString();
                for (int i = 1; i < libraryStatus.Columns.Count; i++)
                {
                    listToAdd.SubItems.Add(librarydr[ColumnsHeaderString[i]].ToString());
                }
                libraryStatus.Items.Add(listToAdd);
            }
            try
            {
                connectionLogin.Close();
            }
            catch (Exception exception)
            {
                MessageBox.Show(exception.StackTrace);
            }
        }
        private void CheckLibrary_Load(object sender, EventArgs e)
        {
            flushLibraryStatus();
        }

        private void libraryStatus_ColumnClick(object sender, ColumnClickEventArgs e)
        {
                if (e.Column == this.sorter.SortColumn)
                {
                    if (this.sorter.SortOrder == SortOrder.Ascending)
                        this.sorter.SortOrder = SortOrder.Descending;
                    else
                    if (this.sorter.SortOrder == SortOrder.Descending)
                        this.sorter.SortOrder = SortOrder.Ascending;
                    else
                    return;
                }
                else
                {
                    this.sorter.SortColumn = e.Column;
                }
                this.libraryStatus.Sort();
            }
        private void btnAddBook_Click(object sender, EventArgs e)
        {
            TrueBook trueBook=new TrueBook();
            bool isConfirmed = true;
            Form tableForm = new AddAndModifyDialogYellow(ref trueBook);
            tableForm.ShowDialog();
            String loginConnection = "server=localhost;user id=root;password=root;database=mountsilverlibraryremake";
            MySqlConnection connectionLogin = new MySqlConnection(loginConnection);
            try
            {
                connectionLogin.Open();
            }
            catch (Exception exception)
            {
                MessageBox.Show(exception.StackTrace);
            }
            String confirm = string.Format("select * from library where ISBN='{0}'", trueBook.ISBN);
            MySqlCommand confirmCommand = new MySqlCommand(confirm, connectionLogin);
            MySqlDataReader confirmrd = confirmCommand.ExecuteReader();
            while (confirmrd.Read())
            {
                isConfirmed = false;
            }
            confirmrd.Close();
            if (isConfirmed)
            {
                String libraryCommand = string.Format("insert into library(name,version,ISBN,author,republic,storage,baseprice,discount,finalprice) " +
                      "values('{0}','{1}','{2}','{3}','{4}',{5},{6},{7},{8})", trueBook.name, trueBook.version, trueBook.ISBN, trueBook.author, trueBook.republic, trueBook.storage, trueBook.baseprice, trueBook.discount, trueBook.finalprice);
                MySqlCommand commandLibrary = new MySqlCommand(libraryCommand, connectionLogin);
                commandLibrary.ExecuteNonQuery();
            }
            else
            {
                MessageBox.Show("禁止添加ISBN号相同的书目，这是无效行为");
            }
            try
            {
                connectionLogin.Close();
            }
            catch (Exception exception)
            {
                MessageBox.Show(exception.StackTrace);
            }
            flushLibraryStatus();
        }
        private void btnDelete_Click(object sender, EventArgs e)
        {
            String loginConnection = "server=localhost;user id=root;password=root;database=mountsilverlibraryremake";
            MySqlConnection connectionLogin = new MySqlConnection(loginConnection);
            try
            {
                connectionLogin.Open();
            }
            catch (Exception exception)
            {
                MessageBox.Show(exception.StackTrace);
            }
            for (int i = 0; i < libraryStatus.SelectedItems.Count; i++)
            {
                String libraryCommand = "delete from library where `ISBN`=" + "\'" + libraryStatus.SelectedItems[i].SubItems[2].Text + "\'";
                MySqlCommand commandLibrary = new MySqlCommand(libraryCommand, connectionLogin);
                commandLibrary.ExecuteNonQuery();
            }
            try
            {
                connectionLogin.Close();
            }
            catch (Exception exception)
            {
                MessageBox.Show(exception.StackTrace);
            }
            flushLibraryStatus();
        }
        private void btnModify_Click(object sender, EventArgs e)
        {
            bool isConfirmed;
            String loginConnection = "server=localhost;user id=root;password=root;database=mountsilverlibraryremake";
            MySqlConnection connectionLogin = new MySqlConnection(loginConnection);
            try
            {
                connectionLogin.Open();
            }
            catch (Exception exception)
            {
                MessageBox.Show(exception.StackTrace);
            }
            for (int i = 0; i < libraryStatus.SelectedItems.Count; i++)
            {
                isConfirmed = true;
                ListViewItem listViewToTrue = libraryStatus.SelectedItems[i];
                String BeforeISBN = listViewToTrue.SubItems[2].Text;
                TrueBook trueBook = new TrueBook(listViewToTrue.SubItems[0].Text, listViewToTrue.SubItems[1].Text, listViewToTrue.SubItems[2].Text,
                    listViewToTrue.SubItems[3].Text, listViewToTrue.SubItems[4].Text, Convert.ToInt32(listViewToTrue.SubItems[5].Text),
                    Convert.ToDouble(listViewToTrue.SubItems[6].Text), Convert.ToDouble(listViewToTrue.SubItems[7].Text), Convert.ToDouble(listViewToTrue.SubItems[8].Text));
                Form tableForm = new AddAndModifyDialogYellow(ref trueBook);
                tableForm.ShowDialog();
                String confirm = string.Format("select * from library where ISBN='{0}'", trueBook.ISBN);
                MySqlCommand confirmCommand = new MySqlCommand(confirm, connectionLogin);
                MySqlDataReader confirmrd = confirmCommand.ExecuteReader();
                while (confirmrd.Read())
                {
                    isConfirmed = false;
                }
                confirmrd.Close();
                if (isConfirmed)
                {
                    String libraryCommand = string.Format("update library set name='{0}',version='{1}',ISBN='{2}',author='{3}'" +
                        ",republic='{4}',storage={5},baseprice={6},discount={7}," +
                        "finalprice={8} where ISBN='{9}'", trueBook.name, trueBook.version,
                        trueBook.ISBN, trueBook.author, trueBook.republic,
                        trueBook.storage, trueBook.baseprice, trueBook.discount, trueBook.finalprice
                        , BeforeISBN);
                    MySqlCommand commandLibrary = new MySqlCommand(libraryCommand, connectionLogin);
                    commandLibrary.ExecuteNonQuery();
                }
                else
                {
                    MessageBox.Show("禁止修改出ISBN号相同的书目，这是无效行为，需要先删除错误的ISBN号");
                }
            }
            try
            {
                connectionLogin.Close();
            }
            catch (Exception exception)
            {
                MessageBox.Show(exception.StackTrace);
            }
            flushLibraryStatus();
        }

        private void btnUSearch_Click(object sender, EventArgs e)
        {
            int selected = selectedLimit.SelectedIndex;
            for(int i = 0; i < libraryStatus.Items.Count;)
            {
                if (!libraryStatus.Items[i].SubItems[selected].Text.Contains(limitation.Text))
                {
                    libraryStatus.Items.RemoveAt(i);
                }
                else
                {
                    i++;
                }
            }
            limitation.Clear();
        }
        private void btnPSearch_Click(object sender, EventArgs e)
        {
            int selected = selectedPeriod.SelectedIndex;
            int sign=-1;
            if (IsDouble(min.Text.Trim()) && IsDouble(max.Text.Trim()))
            {
                sign = 2;
            }else if (IsDouble(min.Text.Trim()))
            {
                sign = 1;
            }else if (IsDouble(max.Text.Trim()))
            {
                sign = 0;
            }
            if (sign == 0) {
                for (int i = 0; i < libraryStatus.Items.Count;)
                {
                    Double data = Convert.ToDouble(libraryStatus.Items[i].SubItems[selected + 5].Text);
                    Double maxData = Convert.ToDouble(max.Text.Trim());
                    if (data>maxData)
                    {
                        libraryStatus.Items.RemoveAt(i);
                    }
                    else
                    {
                        i++;
                    }
                }
            }
            else if (sign == 1)
            {
                for (int i = 0; i < libraryStatus.Items.Count;)
                {
                    Double data = Convert.ToDouble(libraryStatus.Items[i].SubItems[selected + 5].Text);
                    Double minData = Convert.ToDouble(min.Text.Trim());
                    if (data < minData)
                    {
                        libraryStatus.Items.RemoveAt(i);
                    }
                    else
                    {
                        i++;
                    }
                }
            }
            else if (sign == 2)
            {
                for (int i = 0; i < libraryStatus.Items.Count;)
                {
                    Double data = Convert.ToDouble(libraryStatus.Items[i].SubItems[selected + 5].Text);
                    Double maxData = Convert.ToDouble(max.Text.Trim());
                    Double minData = Convert.ToDouble(min.Text.Trim());
                    if (data > maxData|| data < minData)
                    {
                        libraryStatus.Items.RemoveAt(i);
                    }
                    else
                    {
                        i++;
                    }
                }
            }
            min.Clear();
            max.Clear();
        }
        private void btnClear_Click(object sender, EventArgs e)
        {
            flushLibraryStatus();
        }
        private DataSet ExcelToDataSet(string filename)
        {
            DataSet ds;
            string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                            "Extended Properties=Excel 8.0;" +
                            "data source=" + filename;
            OleDbConnection myConn = new OleDbConnection(strCon);
            string strCom = " SELECT * FROM [Sheet1$]";
            myConn.Open();
            OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);
            ds = new DataSet();
            myCommand.Fill(ds);
            myConn.Close();
            return ds;
        }
        private void LibraryImportAsExcel(string strFileName)
        {
            String[] ColumnsHeaderString = { "书名", "版号", "ISBN",
                "作者", "出版社", "库存",
                "基础价格", "折扣", "最终价格" };
            DataTable dt = ExcelToDataSet(strFileName).Tables[0];
            foreach(DataRow row in dt.Rows)
            {
                ListViewItem listToAdd = new ListViewItem();
                listToAdd.Text = row[ColumnsHeaderString[0]].ToString();
                for (int i = 1; i < libraryStatus.Columns.Count; i++)
                {
                    listToAdd.SubItems.Add(row[ColumnsHeaderString[i]].ToString());
                }
                libraryStatus.Items.Add(listToAdd);
            }
        }
        private void LibraryExportAsExcel(string strFileName)
        {
            int rowNumber = libraryStatus.Items.Count;
            int column = libraryStatus.Columns.Count;
            int rowIndex = 1;
            int columnIndex = 0;
            if (rowNumber == 0 || string.IsNullOrEmpty(strFileName))
            {
                return;
            }
            if (rowNumber > 0)
            {
                Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
                if (xlApp == null)
                {
                    MessageBox.Show("无法创建excel,请检查是否安装Excel");
                    return;
                }
                xlApp.DefaultFilePath = "";
                xlApp.DisplayAlerts = true;
                xlApp.SheetsInNewWorkbook = 1;
                Microsoft.Office.Interop.Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
                //将libraryStatus的列名导入Excel表的第一行
                foreach (ColumnHeader dc in libraryStatus.Columns)
                {
                    columnIndex++;
                    xlApp.Cells[rowIndex, columnIndex] = dc.Text;
                }
                //将libraryStatus中的数据导入到Excel中
                for (rowIndex = 2; rowIndex < libraryStatus.Items.Count + 2; rowIndex++)
                {
                    xlApp.Cells[rowIndex, 1] = libraryStatus.Items[rowIndex - 2].Text;
                    for (columnIndex = 2; columnIndex <= libraryStatus.Columns.Count; columnIndex++)
                    {
                        xlApp.Cells[rowIndex, columnIndex] = libraryStatus.Items[rowIndex - 2].SubItems[columnIndex - 1].Text;
                    }
                }
                //例外需要说明的是用strFileName,Excel.XlFileFormat.xlExcel9795保存方式时 当你的Excel版本不是95、97 而是2003、2007 时导出的时候会报一个错误：异常来自 HRESULT:0x800A03EC。 解决办法就是换成strFileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal。 
                xlBook.SaveAs(strFileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                xlApp = null;
                xlBook = null;
                MessageBox.Show("导出成功！");
            }
        }
        private void 导入图书馆ExcelToolStripMenuItem_Click(object sender, EventArgs e)
        {
            System.Windows.Forms.OpenFileDialog ofd = new OpenFileDialog();
            ofd.DefaultExt = "xls";
            ofd.Filter = "Excel文件(*.xls)|*.xls|ExcelNeo(*.xlsx)|*.xlsx";
            if (ofd.ShowDialog() == DialogResult.OK)
            {
                LibraryImportAsExcel(ofd.FileName);
            }
        }
        private void 导出图书馆ExcelToolStripMenuItem_Click(object sender, EventArgs e)
        {
            System.Windows.Forms.SaveFileDialog sfd = new SaveFileDialog();
            sfd.DefaultExt = "xls";
            sfd.Filter = "Excel文件(*.xls)|*.xls|ExcelNeo(*.xlsx)|*.xlsx";
            if (sfd.ShowDialog() == DialogResult.OK)
            {
                LibraryExportAsExcel(sfd.FileName);
            }
        }
    }
    public class easySorter : IComparer
    {
        private Comparer comparer;
        private int sortColumn;
        private SortOrder sortOrder;
        public easySorter()
        {
            sortColumn = 0;
            sortOrder = SortOrder.None;
            comparer = Comparer.Default;
        }
        public int SortColumn
        {
            get { return sortColumn; }
            set { sortColumn = value; }
        }
        public SortOrder SortOrder
        {
            get { return sortOrder; }
            set { sortOrder = value; }
        }
        public int Compare(object x, object y)
        {
            int CompareResult;
            ListViewItem itemX = (ListViewItem)x;
            ListViewItem itemY = (ListViewItem)y;
            CompareResult = comparer.Compare(itemX.SubItems[this.sortColumn].Text, itemY.SubItems[this.sortColumn].Text);
            if (this.SortOrder == SortOrder.Ascending)
                return CompareResult;
            else
            if (this.SortOrder == SortOrder.Descending)
                return (-CompareResult);
            else
                return 0;
        }
    }
}
